﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Microsoft.Reporting.WebForms;
using HK2009.Common;
using HK2009.BusinessFacade;
using HK2009.BusinessFacadeHandler;
using HK2009.EntityQuery;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using System.Threading;
using System.IO;
public partial class Reports_MateReceiptReport : BasePage
{
    DataBase db = new DataBase();
    ISyClientHandler clientManage = new SyClientHandler();
    private static string clientName = string.Empty;
    ISystemHandler systemManage = new SystemHandler();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            FillDDl();
            dataBindClient();
            ViewState["DateFrom"] = DateTime.Now.ToString("yyyy-MM-dd");
            ViewState["DateTo"] = DateTime.Now.ToString("yyyy-MM-dd");
           PreviewReport();
        }
    }


      #region FillDDl
    private void FillDDl()
    {
        //绑定托运人
        DataTable dt = clientManage.GetClientListByTypeID(int.Parse(Enums.CT.TYR.ToString("d")));
        lstShipper.DataSource = dt;

        if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("en"))
        {
            lstShipper.DataTextField = "ENGShortName";
        }
        else if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("zh-hk"))
        {
            lstShipper.DataTextField = "HKShortName";
        }
        else
        {
            lstShipper.DataTextField = "CNShortName";
        }
        lstShipper.DataValueField = "PKID";
        lstShipper.DataBind();

        //工厂
        dt = clientManage.GetClientListByTypeID(int.Parse(Enums.CT.GC.ToString("d")));
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            ListItem item = new ListItem();
            if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("zh-hk"))
            {
                // ddlVendor.DataTextField = "HKShortName";
                item.Text = dt.Rows[i]["ENGShortName"].ToString() + "/" + dt.Rows[i]["HKShortName"].ToString();
            }
            else
            {
                item.Text = dt.Rows[i]["ENGShortName"].ToString() + "/" + dt.Rows[i]["CNShortName"].ToString();
                //ddlVendor.DataTextField = "CNShortName";
            }
            item.Value = dt.Rows[i]["PKID"].ToString();
            // ddlVendor.DataValueField = "PKID";
            lstVendor.Items.Add(item);
        }

        //仓库
        dt = clientManage.GetClientListByTypeID(int.Parse(Enums.CT.CK.ToString("d")));
        lstWhse.DataSource = dt;
        if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("en"))
        {
            lstWhse.DataTextField = "ENGShortName";
        }
        else if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("zh-hk"))
        {
            lstWhse.DataTextField = "HKShortName";
        }
        else
        {
            lstWhse.DataTextField = "CNShortName";
        }
        lstWhse.DataValueField = "PKID";
        lstWhse.DataBind();

        ////客户客人
        //dt = clientManage.GetClientListByTypeID(int.Parse(Enums.CT.KHKR.ToString("d")));
        //lstCustomer.DataSource = dt;
        //if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("en"))
        //{
        //    lstCustomer.DataTextField = "ENGShortName";
        //}
        //else if (Thread.CurrentThread.CurrentUICulture.ToString().ToLower().Equals("zh-hk"))
        //{
        //    lstCustomer.DataTextField = "HKShortName";
        //}
        //else
        //{
        //    lstCustomer.DataTextField = "CNShortName";
        //}
        //lstCustomer.DataValueField = "PKID";
        //lstCustomer.DataBind();
    }
      #endregion

    #region GetDataTable 获取主报表的数据源
    private DataTable GetDataTable()
    {
        SqlParameter[] parms = null;
        List<SqlParameter> lst = new List<SqlParameter>();
        string sqlWhere = string.Empty;
        if (ViewState["DateFrom"] != null && ViewState["DateTo"] != null)
        {
            lst.Add(new SqlParameter("@DateFrom",ViewState["DateFrom"].ToString() + " 00:00:00"));
            lst.Add(new SqlParameter("@DateTo",ViewState["DateTo"].ToString() + " 23:59:59"));
            sqlWhere += " and MateReceiptDate between @DateFrom and @DateTo";
        }
        else if (ViewState["DateFrom"] != null && ViewState["DateTo"] == null)
        {
            lst.Add(new SqlParameter("@DateFrom", ViewState["DateFrom"].ToString() + " 00:00:00"));
            sqlWhere += " and  MateReceiptDate >= @DateFrom ";

        }
        else if (ViewState["DateFrom"] == null && ViewState["DateTo"] != null)
        {
            lst.Add(new SqlParameter("@DateTo", ViewState["DateTo"].ToString() + " 00:00:00"));
            sqlWhere += " and  MateReceiptDate <= @DateTo ";
        }

        if (ViewState["Shipper"] != null)
        {
           // lst.Add(new SqlParameter("@Shipper", ViewState["Shipper"].ToString()));
            sqlWhere += " and ShipperID in (" + ViewState["Shipper"].ToString() + ")";
        }

        if (ViewState["Whse"] != null)
        {
            sqlWhere += " and WhouseID in(" + ViewState["Whse"].ToString() + ")";
        }
        if (ViewState["Vendor"] != null)
        {
            // lst.Add(new SqlParameter("@Shipper", ViewState["Shipper"].ToString()));
            sqlWhere += " and VendorID in (" + ViewState["Vendor"].ToString() + ")";
        }

        if (systemManage.IsCompanyRoles2())//公司用户2
        {
            lst.Add(new SqlParameter("@UserID", Session["UserID"].ToString()));

        }

        parms = lst.ToArray();

        string sqlStr =string.Empty;

        if (systemManage.IsCompanyRoles2())//公司用户2
        {
            sqlStr = @"SELECT SO.*,SODtl.*,

 (select CNShortName from Sy_Client where PKID=SODtl.ShipperID) as ShipperName,
(select top 1 LinkMan from Sy_ClientLinkMan where ClientID=SODtl.ShipperID) as LinkMan,
(select CNShortName from Sy_Client where PKID=SO.VendorID)as VendorName,
(select CNShortName from Sy_Client where PKID=SODtl.WhouseID) as WhouseName 

FROM BN_ShippingOrder as SO 
join BN_ShippingOrderDtl as SODtl  
on SO.PKID=SODtl.OrderID 
join bn_Booking as b 
on SO.BookingID=b.PKID 
join bn_bookingDtl as BDtl
on b.PKID=BDtl.BookingID 
join bn_BookingNoteDtl BookNDtl
on BDtl.BookingNoteDtlID =BookNDtl.PKID
join bn_BookingNote BookN 
on BookNDtl.BookingNoteID=Bookn.PKID
where Bookn.CreatedUser in(
select UserID from Users where  ClientID in(
select ClientID from Users where UserID=@UserID))
and  SO.MateReceiptDate is not null";
        }
        else
        {

            sqlStr = @"SELECT SO.*,SODtl.*,

 (select CNShortName from Sy_Client where PKID=SODtl.ShipperID) as ShipperName,
(select top 1 LinkMan from Sy_ClientLinkMan where ClientID=SODtl.ShipperID) as LinkMan,
(select CNShortName from Sy_Client where PKID=SO.VendorID)as VendorName,
(select CNShortName from Sy_Client where PKID=SODtl.WhouseID) as WhouseName 

FROM BN_ShippingOrder as SO 
join BN_ShippingOrderDtl as SODtl  
on SO.PKID=SODtl.OrderID where  SO.MateReceiptDate is not null ";
        }

        sqlStr += sqlWhere;

        return db.RunSqlTab(sqlStr,parms);
    }
      #endregion

    #region Preview Report
    private void PreviewReport()
    {
        rptViewer.LocalReport.ReportPath = "Reports/MateReceipt.rdlc";

        rptViewer.LocalReport.DataSources.Clear();
        DataTable dt= GetDataTable();
        if (dt != null && dt.Rows.Count > 0)
        {
            rptViewer.Visible = true;
            lblNoRecord.Visible = false;
            ReportDataSource datasource = new ReportDataSource("MateReceipt_DtMateReceipt", dt);
            rptViewer.LocalReport.DataSources.Add(datasource);
            rptViewer.LocalReport.Refresh();
        }
        else
        {
            rptViewer.Visible = false;
            lblNoRecord.Visible = true;
        }
    }
    #endregion

    protected void btnSearch_Click(object sender, EventArgs e)
    {
        if (txtMateReceiptDateFrom.Text != string.Empty)
        {
            ViewState["DateFrom"] = txtMateReceiptDateFrom.Text.Trim();
        }
        else
        {
            ViewState["DateFrom"] = null;
        }
        if (txtMateReceiptDateTo.Text != string.Empty)
        {
            ViewState["DateTo"] = txtMateReceiptDateTo.Text.Trim();
        }
        else
        {
            ViewState["Dateto"] = null;
        }

        if (lstSelShipper.Items.Count > 0)
        {
            string item = string.Empty;
            for (int i = 0; i < lstSelShipper.Items.Count; i++)
            {
                item += lstSelShipper.Items[i].Value + ",";
            }
            ViewState["Shipper"] = item.Substring(0, item.Length - 1);
        }
        else
        {
            ViewState["Shipper"] = null;
        }

        if (lstSelVendor.Items.Count > 0)
        {
            string item = string.Empty;
            for (int i = 0; i < lstSelVendor.Items.Count; i++)
            {
                item += lstSelVendor.Items[i].Value + ",";
            }
            ViewState["Vendor"] = item.Substring(0, item.Length - 1);
        }
        else
        {
            ViewState["Vendor"] = null;
        }

        if (lstSelWhse.Items.Count > 0)
        {
            string item = string.Empty;
            for (int i = 0; i < lstSelWhse.Items.Count; i++)
            {
                item += lstSelWhse.Items[i].Value + ",";
            }
            ViewState["Whse"] = item.Substring(0, item.Length - 1);
        }
        else
        {
            ViewState["Whse"] = null;
        }
      
        PreviewReport();
    }

    #region 调出框用到的所有方法
    private void dataBindClient()
    {
        ViewState["OrderBy"] = "Status";
        ViewState["Accending"] = "desc";      

        string sqlStr = "SELECT S.* FROM Sy_Client as S,Sy_ClientType as ST where S.PKID =ST.ClientID and Status=1 and ST.SyCodeID=" + Enums.CT.TYR.ToString("d");
        SqlParameter[] parms = null;
        string clause = string.Empty;
        ProcessParms(ref clause, ref parms);
        sqlStr += clause;

        DataTable dt = clientManage.GetClientList(sqlStr, parms);
        DataView view = dt.DefaultView;
        view.Sort = ViewState["OrderBy"] + " " + ViewState["Accending"];
        gvClientList.DataSource = view;
        gvClientList.DataBind();
        if (dt != null && dt.Rows.Count > 0)
        {
            lblTotal.Text = dt.Rows.Count.ToString();
        }
        else
        {
            lblTotal.Text = "0";
        }

    }

    /// <summary>
    /// 处理传入参数。。返回SQL条件和sqlparameter
    /// </summary>
    /// <param name="sqlstr"></param>
    /// <param name="parms"></param>
    /// <summary>
    /// 处理传入参数。。返回SQL条件和sqlparameter
    /// </summary>
    /// <param name="sqlstr"></param>
    /// <param name="parms"></param>
    private void ProcessParms(ref string sqlstr, ref SqlParameter[] parms)
    {
        List<SqlParameter> list = new List<SqlParameter>();
        sqlstr = "";
        if (ViewState["Value"] != null)
        {
            list.Add(new SqlParameter("@ColumnName", ViewState["Value"].ToString()));
            sqlstr += " and " + ViewState["Name"].ToString() + " like '%' +  @ColumnName + '%'";
        }     

        parms = list.ToArray();
    }  


    protected void gvClientList_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvClientList.PageIndex = e.NewPageIndex;
        dataBindClient();
    }

    protected void gvClientList_Sorting(object sender, GridViewSortEventArgs e)
    {
        if (e.SortExpression.ToLower() == ViewState["OrderBy"].ToString().ToLower())
        {
            if (ViewState["Accending"].ToString().ToLower() == "asc")
            {
                ViewState["Accending"] = "desc";
            }
            else
            {
                ViewState["Accending"] = "asc";
            }
        }
        else
        {
            ViewState["OrderBy"] = e.SortExpression;
        }

        dataBindClient();
    }

    protected void btnShipperSel_Click(object sender, EventArgs e)
    {

        if (txtName.Text != string.Empty)
        {
            ViewState["Value"] = txtName.Text.Trim();
            ViewState["Name"] = ddlColumnName.SelectedValue;
        }
        else
        {
            ViewState["Value"] = null;
            ViewState["Name"] = null;
        }
      
        dataBindClient();

    }

    protected void lbtnGo_Click(object sender, EventArgs e)
    {
        TextBox txtPages = ((TextBox)gvClientList.BottomPagerRow.FindControl("txtPages"));
        try
        {
            int i = Convert.ToInt32(txtPages.Text) - 1;
            if (i >= 0 && i < gvClientList.PageCount)
            {
                this.gvClientList.PageIndex = i;
                //数据绑定
                dataBindClient();
            }
            else
            {
                Jscript.AlertInAjax("请输入大于0小于总页数的正整数!", UpdatePanel1);
                return;
            }
        }
        catch
        {
            Jscript.AlertInAjax("请输入大于0小于总页数的正整数!", UpdatePanel1);
        }
    }
    protected void ddlPages_SelectedIndexChanged(object sender, EventArgs e)
    {
        gvClientList.PageSize = int.Parse(ddlPages.SelectedValue);
        dataBindClient();
    }


    protected void btnClose_Click(object sender, EventArgs e)
    {
        CloseSelectRoles();
    }

    protected void btnSub_Click(object sender, EventArgs e)
    {
        if (Request["chkSelected"] != null)
        {
            lblError.Visible = false;
            hidClientID.Value = Request["chkSelected"];
            int id = 0;
            int.TryParse(Request["chkSelected"], out id);
            txtshipperName.Text = GetClientName(id);
            CloseSelectRoles();

        }
        else
        {
            lblError.Visible = true;
        }
    }

    private string GetClientName(int id)
    {
        string name = string.Empty;
        Sy_ClientQuery query = clientManage.GetClient(id);
        if (query != null)
        {
            name = query.CNName;
        }
        return name;
    }

    /// <summary>
    ///
    /// </summary>
    private void CloseSelectRoles()
    {
        myDiv.Style.Add("display", "none");
        divError.Style.Add("display", "none");
    }


    protected void lbtnSelect_Click(object sender, EventArgs e)
    {
        myDiv.Style.Add("display", "block");
        divError.Style.Add("display", "block");
    }
    #endregion


    protected void btnAdd_Click(object sender, EventArgs e)
    {
        if (lstShipper.SelectedValue != string.Empty)
        {
            for (int i = 0; i < lstShipper.Items.Count; i++)
            {
                bool issel = false;
                if (lstShipper.Items[i].Selected)
                {
                    for (int j = 0; j < lstSelShipper.Items.Count; j++)
                    {
                        if (lstShipper.Items[i].Value == lstSelShipper.Items[j].Value)
                        {
                            issel = true;
                            break;
                        }
                    }

                    if (!issel)
                    {
                        ListItem item = new ListItem();
                        item.Text = lstShipper.Items[i].Text;
                        item.Value = lstShipper.Items[i].Value;
                        lstSelShipper.Items.Add(item);
                    }
                }
            }
        }
        else
        {

        }
    }

    protected void btnDelete_Click(object sender, EventArgs e)
    {
        if (lstSelShipper.Items.Count > 0)
        {
            ListItemCollection lstColl = new ListItemCollection();
            for (int i = 0; i < lstSelShipper.Items.Count; i++)
            {
                if (!lstSelShipper.Items[i].Selected)
                {
                    ListItem item = new ListItem(lstSelShipper.Items[i].Text, lstSelShipper.Items[i].Value);
                    lstColl.Add(item);
                }
            }
            if (lstColl != null && lstColl.Count > 0)
            {
                lstSelShipper.Items.Clear();
                for (int j = 0; j < lstColl.Count; j++)
                {
                    lstSelShipper.Items.Add(lstColl[j]);
                }
            }
            else
            {
                lstSelShipper.Items.Clear();
            }

        }
    }

    //protected void btnCustomerAdd_Click(object sender, EventArgs e)
    //{
    //    if (lstCustomer.SelectedValue != string.Empty)
    //    {
    //        for (int i = 0; i < lstCustomer.Items.Count; i++)
    //        {
    //            bool issel = false;
    //            if (lstCustomer.Items[i].Selected)
    //            {
    //                for (int j = 0; j < lstSelCustomer.Items.Count; j++)
    //                {
    //                    if (lstCustomer.Items[i].Value == lstSelCustomer.Items[j].Value)
    //                    {
    //                        issel = true;
    //                        break;
    //                    }
    //                }

    //                if (!issel)
    //                {
    //                    ListItem item = new ListItem();
    //                    item.Text = lstCustomer.Items[i].Text;
    //                    item.Value = lstCustomer.Items[i].Value;
    //                    lstSelCustomer.Items.Add(item);
    //                }
    //            }
    //        }
    //    }
    //    else
    //    {

    //    }
    //}

    //protected void btnCustomerDel_Click(object sender, EventArgs e)
    //{
    //    if (lstSelCustomer.Items.Count > 0)
    //    {
    //        ListItemCollection lstColl = new ListItemCollection();
    //        for (int i = 0; i < lstSelCustomer.Items.Count; i++)
    //        {
    //            if (!lstSelCustomer.Items[i].Selected)
    //            {
    //                ListItem item = new ListItem(lstSelCustomer.Items[i].Text, lstSelCustomer.Items[i].Value);
    //                lstColl.Add(item);
    //            }
    //        }
    //        if (lstColl != null && lstColl.Count > 0)
    //        {
    //            lstSelCustomer.Items.Clear();
    //            for (int j = 0; j < lstColl.Count; j++)
    //            {
    //                lstSelCustomer.Items.Add(lstColl[j]);
    //            }
    //        }
    //        else
    //        {
    //            lstSelCustomer.Items.Clear();
    //        }

    //    }
    //}

    protected void btnWhseAdd_Click(object sender, EventArgs e)
    {
        if (lstWhse.SelectedValue != string.Empty)
        {
            for (int i = 0; i < lstWhse.Items.Count; i++)
            {
                bool issel = false;
                if (lstWhse.Items[i].Selected)
                {
                    for (int j = 0; j < lstSelWhse.Items.Count; j++)
                    {
                        if (lstWhse.Items[i].Value == lstSelWhse.Items[j].Value)
                        {
                            issel = true;
                            break;
                        }
                    }

                    if (!issel)
                    {
                        ListItem item = new ListItem();
                        item.Text = lstWhse.Items[i].Text;
                        item.Value = lstWhse.Items[i].Value;
                        lstSelWhse.Items.Add(item);
                    }
                }
            }
        }
        else
        {

        }
    }

    protected void btnWhseDel_Click(object sender, EventArgs e)
    {
        if (lstSelWhse.Items.Count > 0)
        {
            ListItemCollection lstColl = new ListItemCollection();
            for (int i = 0; i < lstSelWhse.Items.Count; i++)
            {
                if (!lstSelWhse.Items[i].Selected)
                {
                    ListItem item = new ListItem(lstSelWhse.Items[i].Text, lstSelWhse.Items[i].Value);
                    lstColl.Add(item);
                }
            }
            if (lstColl != null && lstColl.Count > 0)
            {
                lstSelWhse.Items.Clear();
                for (int j = 0; j < lstColl.Count; j++)
                {
                    lstSelWhse.Items.Add(lstColl[j]);
                }
            }
            else
            {
                lstSelWhse.Items.Clear();
            }

        }
    }

    protected void btnVendorAdd_Click(object sender, EventArgs e)
    {
        if (lstVendor.SelectedValue != string.Empty)
        {
            for (int i = 0; i < lstVendor.Items.Count; i++)
            {
                bool issel = false;
                if (lstVendor.Items[i].Selected)
                {
                    for (int j = 0; j < lstSelVendor.Items.Count; j++)
                    {
                        if (lstVendor.Items[i].Value == lstSelVendor.Items[j].Value)
                        {
                            issel = true;
                            break;
                        }
                    }

                    if (!issel)
                    {
                        ListItem item = new ListItem();
                        item.Text = lstVendor.Items[i].Text;
                        item.Value = lstVendor.Items[i].Value;
                        lstSelVendor.Items.Add(item);
                    }
                }
            }
        }
        else
        {

        }
    }

    protected void btnVendorDel_Click(object sender, EventArgs e)
    {
        if (lstSelVendor.Items.Count > 0)
        {
            ListItemCollection lstColl = new ListItemCollection();
            for (int i = 0; i < lstSelVendor.Items.Count; i++)
            {
                if (!lstSelVendor.Items[i].Selected)
                {
                    ListItem item = new ListItem(lstSelVendor.Items[i].Text, lstSelVendor.Items[i].Value);
                    lstColl.Add(item);
                }
            }
            if (lstColl != null && lstColl.Count > 0)
            {
                lstSelVendor.Items.Clear();
                for (int j = 0; j < lstColl.Count; j++)
                {
                    lstSelVendor.Items.Add(lstColl[j]);
                }
            }
            else
            {
                lstSelVendor.Items.Clear();
            }

        }
    }

    protected void btnEmail_Click(object sender, EventArgs e)
    {
        if (this.rptViewer.LocalReport.DataSources.Count > 0)
        {
            Microsoft.Reporting.WebForms.Warning[] Warnings;
            string[] strStreamIds;
            string strMimeType;
            string strEncoding;
            string strFileNameExtension;

            byte[] bytes = this.rptViewer.LocalReport.Render("Excel", null, out strMimeType, out strEncoding, out strFileNameExtension, out strStreamIds, out Warnings);

            // string strFilePath = @"D:\report.xls";
            string filename = FileTools.AutoFileName() + ".xls";
            string strFilePath = "MateReceiptReport_" + filename;
            using (System.IO.FileStream fs = new FileStream(Server.MapPath("~").ToString() + "/EmailReport/" + strFilePath, FileMode.Create))
            {
                fs.Write(bytes, 0, bytes.Length);
            }

            Response.Redirect("~/SystemManage/EmailClient.aspx?atm=" + strFilePath);
        }
        else
        {

        }
    }
}
